﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.Data.SqlClient;

namespace Payroll
{
    class myLINQ
    {
        public static string PaiedLINQ(string m,string d)
        {
            string sqlStr;
            string connString = @"Data Source=..\..\Database1.sdf";
            SqlCeConnection connection = new SqlCeConnection(connString);
            connection.Open();
            sqlStr = "select * from payemployee order by payDate";
            SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
            SqlCeDataReader result = cmd.ExecuteReader();

            List<Paied> list1 = new List<Paied>();

            while (result.Read())//数据库写入列表
            {
                string id = Convert.ToString(result[0]);
                string pay = Convert.ToString(result[1]);
                DateTime time = Convert.ToDateTime(result[2]);
                string time2 = time.ToString("yyyy-MM-dd");
                string methods = Convert.ToString(result[3]);

                list1.Add(new Paied { ID = id, PAY = pay, TIME = time2, METHOD = methods });
            }

            connection.Close();

            string records = "";

            if (m == "" && d == "")
            {
                var query = from record in list1
                            select record;

                foreach (var item in query)
                {
                    records += item.ID + " " + item.PAY + " " + item.METHOD + " " + item.TIME + "\r\n";
                }
            }
            else if (d == "")
            {
                var query = from record in list1
                            where record.METHOD == m
                            select record;
                foreach (var item in query)
                {
                    records += item.ID + " " + item.PAY + " " + item.METHOD + " " + item.TIME + "\r\n";
                }
            }
            else if (m == "")
            {
                var query = from record in list1
                            where record.TIME == d
                            select record;
                foreach (var item in query)
                {
                    records += item.ID + " " + item.PAY + " " + item.METHOD + " " + item.TIME + "\r\n";
                }
            }
            else
            {
                var query = from record in list1
                            where record.TIME == d && record.METHOD == m
                            select record;
                foreach (var item in query)
                {
                    records += item.ID + " " + item.PAY + " " + item.METHOD + " " + item.TIME + "\r\n";
                }
            }


            return records;
        }
    }

    public class Paied
    {
        public string ID { get; set; }
        public string METHOD { get; set; }
        public string PAY { get; set; }
        public string TIME { get; set; }
    }
}
